# _*_ coding: utf-8 _*_
from odoo import models, fields, api, tools


class StockQuantReport(models.Model):
    _name = "stock.quant.report"
    _auto = False
    _description = u"库存计价报表"
    _rec_name = "product_id"

    categ_id = fields.Many2one("product.category", string=u"产品分类", readonly=True)
    product_id = fields.Many2one('product.product', u'产品', readonly=True)
    qty = fields.Float(u'数量', readonly=True)
    uom_id = fields.Many2one('product.uom', u'单位', readonly=True)
    location_id = fields.Many2one('stock.location', u'库位', readonly=True)
    lot_id = fields.Many2one('stock.production.lot', u'批次', readonly=True)
    ref = fields.Char(string=u"追踪号", readonly=True)
    lot_lenth = fields.Char(string=u"全长", readonly=True)
    code = fields.Char(string=u"代码", readonly=True)
    description = fields.Char(string=u"说明", readonly=True)
    board_number = fields.Char(string=u"内部参考", readonly=True)
    lot_uom = fields.Many2one('product.uom', u'辅助单位', readonly=True)
    lot_uom_ratio = fields.Float(u"换算率", readonly=True)
    lot_uom_qty = fields.Float(u'辅助单位数量', readonly=True)
    reservation_id = fields.Many2one("stock.move", string=u"预留给移动", readonly=True)
    origin = fields.Char(string=u"预留源单号", readonly=True)
    picking_id = fields.Many2one("stock.picking", string=u"预留调拨单号", readonly=True)
    date = fields.Date(string=u"入库日期", readonly=True)
    cost = fields.Float(string=u"库存价值", groups="account.group_account_user", readonly=True)

    def init(self):
        tools.drop_view_if_exists(self._cr, 'stock_quant_report')
        self._cr.execute("""
            CREATE VIEW stock_quant_report AS (
                select 
                    q.id,
                    t.categ_id,
                    q.product_id,
                    q.qty,
                    t.uom_id,
                    q.location_id,
                    q.lot_id,
                    l.ref,
                    l.lot_lenth,
                    l.code, 
                    l.description,
                    l.board_number,
                    l.lot_uom,
                    l.lot_uom_ratio,
                    case when l.lot_uom_ratio =0 then 0 else q.qty / l.lot_uom_ratio end as lot_uom_qty,
                    q.reservation_id,
                    m.origin,
                    m.picking_id,
                    to_date(to_char(q.in_date,'yyyy-mm-dd'),'yyyy-mm-dd') as date,
                    q.cost 
                from stock_quant q
                    join product_product p on p.id = q.product_id
                    join product_template t on t.id=p.product_tmpl_id
                    left join stock_move m on m.id = q.reservation_id
                    left join stock_production_lot l on l.id = q.lot_id
                    join stock_location c on c.id = q.location_id
                where 
                    c.usage = 'internal'
            )""")
